/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mytools;

import java.sql.*;
import java.util.*;

/**
 *
 * @author wl
 */
public class judgeDAO {

    private Connection con;

    public judgeDAO() throws Exception {
    }

    public List<String> getjudgesubject() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct subject from judgeques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("subject"));
        }
        Collections.sort(list);
        con.close();
        return list;

    }

    public List<String> getjudgechapter() throws Exception {
        con = dbcon.getConnection();
        List<String> list = new ArrayList<String>();
        PreparedStatement ps = con.prepareStatement("select distinct chapter from judgeques");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            list.add(rs.getString("chapter"));
        }
        Collections.sort(list);
        con.close();
        return list;
    }

    public int getscljudgeAmount(String subject, String chapter, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from judgeques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%')  and level like('%" + level + "%')";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<judge> getscljudgelist(int pagenumber, int pagesize, String subject, String chapter, String level) throws SQLException, Exception {
        con = dbcon.getConnection();
        List<judge> list = new ArrayList<judge>();
        String sql = "select * from judgeques where subject like('%" + subject + "%') and chapter like('%" + chapter + "%') and level like('%" + level + "%') ORDER BY id DESC  limit ?,?";
        PreparedStatement ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            judge judge = new judge();
            judge.setId(rs.getInt("id"));
            judge.setName(rs.getString("name"));
            judge.setAnswer(rs.getString("answer"));
            judge.setType(rs.getString("type"));
            judge.setLevel(rs.getString("level"));
            judge.setSubject(rs.getString("subject"));
            judge.setChapter(rs.getString("chapter"));
            judge.setChapterinfo(rs.getString("chapterinfo"));
            list.add(judge);
        }
        rs.close();
        con.close();
        return list;
    }

    public judge getjudge(int id) throws SQLException, Exception {
        con = dbcon.getConnection();
        judge judge = null;
        String sql = "select * from judgeques where id=?";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setInt(1, id);
        ResultSet rs = psmt.executeQuery();
        if (rs.next()) {
            judge = new judge();
            judge.setId(id);
            judge.setName(rs.getString("name"));
            judge.setAnswer(rs.getString("answer"));
            judge.setChapter(rs.getString("chapter"));
            judge.setChapterinfo(rs.getString("chapterinfo"));
            judge.setLevel(rs.getString("level"));
            judge.setSubject(rs.getString("subject"));
            judge.setType(rs.getString("type"));
        }
        con.close();
        return judge;
    }

    public int deletejudge(int id) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        Statement st = con.createStatement();
        i = st.executeUpdate("delete from judgeques where id=' " + id + " '");
        con.close();
        return i;
    }

    public int editjudge(judge judge) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "update judgeques set name=?,answer=?,level=?,subject=?,chapter=?,chapterinfo=? where id=?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ps.setString(1, judge.getName());
        ps.setString(2, judge.getAnswer());
        ps.setString(3, judge.getLevel());
        ps.setString(4, judge.getSubject());
        ps.setString(5, judge.getChapter());
        ps.setString(6, judge.getChapterinfo());
        ps.setInt(7, judge.getId());
        i = ps.executeUpdate();
        con.close();
        return i;
    }

    public int addjudge(judge judge) throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "insert into judgeques value(null,?,?,?,?,?,?,?)";
        PreparedStatement psmt = null;
        psmt = con.prepareStatement(sql);
        psmt.setString(1, judge.getName());
        psmt.setString(2, judge.getAnswer());
        psmt.setString(3, "判断题");
        psmt.setString(4, judge.getLevel());
        psmt.setString(5, judge.getSubject());
        psmt.setString(6, judge.getChapter());
        psmt.setString(7, judge.getChapterinfo());
        i = psmt.executeUpdate();
        con.close();
        return i;

    }

    public int getjudgeAmount() throws SQLException, Exception {
        con = dbcon.getConnection();
        int i = 0;
        String sql = "select count(*) from judgeques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }

        con.close();
        return i;
    }

    public List<judge> getjudgelist(int pagenumber, int pagesize) throws SQLException, Exception {
        con = dbcon.getConnection();
        List<judge> list = new ArrayList<judge>();
        String sql = "select * from judgeques limit ?,?";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        if (pagenumber < 1) {
            pagenumber = 1;
        }
        ps.setInt(1, (pagenumber - 1) * pagesize);
        ps.setInt(2, pagesize);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            judge judge = new judge();
            judge.setId(rs.getInt("id"));
            judge.setName(rs.getString("name"));
            judge.setAnswer(rs.getString("answer"));
            judge.setType(rs.getString("type"));
            judge.setLevel(rs.getString("level"));
            judge.setSubject(rs.getString("subject"));
            judge.setChapter(rs.getString("chapter"));
            judge.setChapterinfo(rs.getString("chapterinfo"));
            list.add(judge);
        }
        rs.close();
        con.close();
        return list;
    }

    public List<judge> judgelist() throws SQLException, Exception {
        con = dbcon.getConnection();
        List<judge> list = new ArrayList<judge>();
        String sql = "select * from judgeques";
        PreparedStatement ps = null;
        ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            judge judge = new judge();
            judge.setId(rs.getInt("id"));
            judge.setName(rs.getString("name"));
            judge.setAnswer(rs.getString("answer"));
            judge.setType(rs.getString("type"));
            judge.setLevel(rs.getString("level"));
            judge.setSubject(rs.getString("subject"));
            judge.setChapter(rs.getString("chapter"));
            judge.setChapterinfo(rs.getString("chapterinfo"));
            list.add(judge);
        }
        rs.close();
        con.close();
        return list;
    }
}
